﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

    public class ConnectDB
    {
        public DataTable getDataFromXLS(string strFilePath)
        {
            try
            {
                string strConnectionString = "";

                strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties=Excel 12.0;";
                OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
                cnCSV.Open();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Sheet1$]", cnCSV);
                OleDbDataAdapter daCSV = new OleDbDataAdapter(); 
                daCSV.SelectCommand = cmdSelect;
                DataTable dtCSV = new DataTable();
                daCSV.Fill(dtCSV);
                cnCSV.Close();
                daCSV = null;
                return dtCSV;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
            }
        }
        public void writeDataToXLS(string strFilePath,List<string> field)
        {
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties=Excel 12.0;");
            MyConnection.Open();
            myCommand.Connection = MyConnection;
            string sheetname = "[Sheet1$]";
            string fN = null;
            string valueField = null;
            for (int i = 0; i < field.Count; i++)
            {
                char fieldName = Convert.ToChar((i + 65));
                if (i < field.Count - 1)
                {
                    fN += fieldName + ",";
                    if (field[i] == null)
                    {
                        valueField += "'  " + "',";
                    }
                    else
                        valueField += "'" + field[i].ToString() + "',";
                }
                else
                {
                    fN += fieldName;
                    if (field[i] == null)
                    {
                        valueField += "'  '";
                    }
                    else
                        valueField += "'" + field[i].ToString() + "'";
                }
            }
            for (int i = 0; i < field.Count; i++)
            {
                sql = "Insert into " + sheetname + " (" + fN + ") values (" + valueField + ")";
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
            }
            MyConnection.Close();         

        }
    }

